/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package alwv.reports.pdf;

import alwv.database.DerbyConnection;
import alwv.database.Query;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Brad
 */
public class YearlyReportByMonth {

    private static final float LEFT = 0.25f;
    private static final float RIGHT = 0.25f;
    private static final float TOP = 50.0f;
    private static final float BOTTOM = 50.0f;

    public static void createYearlyReportByMonth(Connection conn, int year, String filename) {
        try {
            Document d = new Document(PageSize.A4.rotate());
            d.setMargins(LEFT, RIGHT, TOP, BOTTOM);

            PdfWriter writer = PdfWriter.getInstance(d, new FileOutputStream(filename));
            d.open();

            PdfPTable table = new PdfPTable(14);
            float[] widths = {55f, 17f, 17f, 19f, 17f, 20f, 20f, 20f, 22f, 17f, 17f, 17f, 17f, 17f};
            //table.setLockedWidth(true);
            table.setWidths(widths);
            table.setWidthPercentage(90f);
            table.setHeaderRows(1);

            table.addCell(headerCell(""));
            table.addCell(headerCell("July"));
            table.addCell(headerCell("August"));
            table.addCell(headerCell("September"));
            table.addCell(headerCell("October"));
            table.addCell(headerCell("November"));
            table.addCell(headerCell("December"));
            table.addCell(headerCell("January"));
            table.addCell(headerCell("February"));
            table.addCell(headerCell("March"));
            table.addCell(headerCell("April"));
            table.addCell(headerCell("May"));
            table.addCell(headerCell("June"));
            table.addCell(headerTotalCell("TOTAL"));

            addRow(table, 14);
            
            // Insert Income Data
            
            addTitleRow(table, "INCOME", 13);
           
            ArrayList<Object> info = Query.getIncomeTransactionsForYear(conn, year);
           
            for(int i = 0; i < info.size(); i++){
                if(info.get(i) instanceof String){
                    table.addCell(categoryCell((String)info.get(i)));
                }
                else{
                    table.addCell(dollarAmountCell(String.valueOf(info.get(i))));
                }
            }
            
            table.addCell(categoryCell("FROM Education Fund"));
                       
            int currentMonth = 7;
            int currentYear = year;
            for(int i = 0; i < 12; i++){
                String amount = String.valueOf(Query.getEducationFundIncomeTransactionMonthlyTotal(conn, currentMonth, currentYear));
                table.addCell(totalDollarCell(amount));
                currentMonth++;
                if(currentMonth == 13){
                    currentMonth = 1;
                    currentYear++;
                }
            }
            
            table.addCell(totalDollarCell(String.valueOf(Query.getEducationFundIncomeTransactionTotalFiscalYear(conn, year))));
            
            table.addCell(totalTitleCell("TOTAL INCOME"));
            
            currentMonth = 7;
            currentYear = year;
            for(int i = 0; i < 12; i++){
                String amount = String.valueOf(Query.getMonthlyIncomeTotal(conn, currentMonth, currentYear));
                table.addCell(totalDollarCell(amount));
                currentMonth++;
                if(currentMonth == 13){
                    currentMonth = 1;
                    currentYear++;
                }
            }
            
            table.addCell(totalDollarCell(String.valueOf(Query.getIncomeFiscalYearTotal(conn, year))));
            
            // Insert Expense data
            
            addRow(table, 14);
            
            addTitleRow(table, "EXPENSES", 13);
            
            info = Query.getExpenseTransactionsForYear(conn, year);
           
            for(int i = 0; i < info.size(); i++){
                if(info.get(i) instanceof String){
                    table.addCell(categoryCell((String)info.get(i)));
                }
                else{
                    table.addCell(dollarAmountCell(String.valueOf(info.get(i))));
                }
            }
            
            table.addCell(totalTitleCell("TOTAL EXPENSES"));
            
            currentMonth = 7;
            currentYear = year;
            for(int i = 0; i < 12; i++){
                String amount = String.valueOf(Query.getMonthlyExpenseTotal(conn, currentMonth, currentYear));
                table.addCell(totalDollarCell(amount));
                currentMonth++;
                if(currentMonth == 13){
                    currentMonth = 1;
                    currentYear++;
                }
            }
       
            table.addCell(totalDollarCell(String.valueOf(Query.getExpenseFiscalYearTotal(conn, year))));
            
            // Add Page title information
            Font f = new Font(Font.FontFamily.TIMES_ROMAN, 12, Font.BOLD);
            Paragraph p = new Paragraph("Appleton League of Women Voters",f);
            p.setAlignment(Element.ALIGN_CENTER);
            p.setSpacingAfter(5f);            
            d.add(p);
            
            p = new Paragraph("Financial Report " + year + " - " + ((String.valueOf(year+1)).substring(2,4)),f);
            p.setAlignment(Element.ALIGN_CENTER);
            p.setSpacingAfter(15f);
            d.add(p);
            
            d.add(table);

            d.close();

            File file = new File(filename);
            Desktop.getDesktop().open(file);

        } catch (Exception ex) {
            Logger.getLogger(YearlyReportByMonth.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static PdfPCell headerCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        //cell.addElement();
        return cell;
    }

    private static PdfPCell headerTotalCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        //cell.addElement();
        return cell;
    }
    
    private static PdfPCell categoryCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        //cell.addElement();
        return cell;
    }
    
    private static PdfPCell dollarAmountCell(String s) {
        if(s.contains(".")){
            if(s.indexOf(".") == s.length() - 2){
                s += "0";
            }
        }
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        //cell.addElement();
        return cell;
    }
    
    private static PdfPCell titleCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorderWidthBottom(2.0f);
        cell.setFixedHeight(20f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        //cell.addElement();
        return cell;
    }
    
    private static PdfPCell titleRowCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorderWidthBottom(2.0f);
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        //cell.addElement();
        return cell;
    }
    
    private static PdfPCell totalTitleCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorderWidthTop(2.0f);
        cell.setBorderWidthBottom(2.0f);
        cell.setFixedHeight(20f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        //cell.addElement();
        return cell;
    }
    
    private static PdfPCell totalDollarCell(String s) {
        if(s.contains(".")){
            if(s.indexOf(".") == s.length() - 2){
                s += "0";
            }
        }
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorderWidthTop(2.0f);
        cell.setBorderWidthBottom(2.0f);
        cell.setFixedHeight(20f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        //cell.addElement();
        return cell;
    }
    
    private static void addRow(PdfPTable t, int columns){
        for(int i = 0; i < columns; i++){
            t.addCell(dollarAmountCell(""));
        }
    }
    
    private static void addTotalRow(PdfPTable t, String category, int columns){
        t.addCell(titleCell(category));
        for(int i = 0; i < columns; i++){
            t.addCell(dollarAmountCell(""));
        }
    }
    
    private static void addTitleRow(PdfPTable t, String title, int columns){
        t.addCell(titleCell(title));
        for(int i = 0; i < columns; i++){
            t.addCell(titleRowCell(""));
        }
    }
    
}
